﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MagicBag.CRM.IDAL;
using MagicBag.CRM.Model;
using MagicBag.CRM.Common;


namespace MagicBag.CRM.SqlServerDAL
{

    public class RoleMenuDAL : IRoleMenu
    {

        public int Insert(RoleMenu roleMenu)
        {
            string sql = "insert into [dbo].[RoleMenu] ([RoleID] ,[MenuID]) values(@RoleID ,@MenuID)";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@RoleID", SqlDbType.Int);
            paras[1] = new SqlParameter("@MenuID", SqlDbType.Int);

            paras[0].Value = roleMenu.RoleID;
            paras[1].Value = roleMenu.MenuID;

            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }



        public int Delete(int roleID, int menuID)
        {
            string sql = "delete from [dbo].[RoleMenu] where [RoleID]=@RoleID and [MenuID]=@MenuID";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@RoleID", SqlDbType.Int);
            paras[1] = new SqlParameter("@MenuID", SqlDbType.Int);

            paras[0].Value = roleID;
            paras[1].Value = menuID;

            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Delete(int roleID)
        {
            string sql = "delete from [dbo].[RoleMenu] where [RoleID]=@RoleID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@RoleID", SqlDbType.Int);
            paras[0].Value = roleID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }


        public int Update(RoleMenu roleMenu)
        {
            string sql = "update [dbo].[RoleMenu] set [LastUpdateUserID]=@LastUpdateUserID,[LastUpdateDate]=@LastUpdateDate where [RoleID]=@RoleID and [MenuID]=@MenuID";
            SqlParameter[] paras = new SqlParameter[4];
            paras[0] = new SqlParameter("@LastUpdateUserID", SqlDbType.VarChar, 50);
            paras[1] = new SqlParameter("@LastUpdateDate", SqlDbType.DateTime);
            paras[2] = new SqlParameter("@RoleID", SqlDbType.Int);
            paras[3] = new SqlParameter("@MenuID", SqlDbType.Int);

            if (string.IsNullOrEmpty(roleMenu.LastUpdateUserID))
                paras[0].Value = DBNull.Value;
            else
                paras[0].Value = roleMenu.LastUpdateUserID;
            paras[1].Value = roleMenu.LastUpdateDate;
            paras[2].Value = roleMenu.RoleID;
            paras[3].Value = roleMenu.MenuID;

            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public IList<RoleMenu> GetRoleMenus()
        {
            IList<RoleMenu> list = new List<RoleMenu>();

            string sql = " SELECT [RoleID] ,[MenuID],[CreateUserID] ,[CreateDate] ,[LastUpdateUserID] ,[LastUpdateDate]  FROM [dbo].[RoleMenu]";

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    RoleMenu roleMenu = new RoleMenu();
                    roleMenu.RoleID = sdr.GetInt32(0);
                    roleMenu.MenuID = sdr.GetInt32(1);
                    roleMenu.CreateUserID = sdr.IsDBNull(2) ? null : sdr.GetString(2);
                    roleMenu.CreateDate = sdr.IsDBNull(3) ? DateTime.MinValue : sdr.GetDateTime(3);
                    roleMenu.LastUpdateUserID = sdr.IsDBNull(4) ? null : sdr.GetString(4);
                    roleMenu.LastUpdateDate = sdr.IsDBNull(5) ? DateTime.MinValue : sdr.GetDateTime(5);

                    list.Add(roleMenu);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取指定角色的所有菜单
        /// </summary>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public IList<RoleMenu> GetRoleMenusByRoleID(int roleID)
        {
            IList<RoleMenu> list = new List<RoleMenu>();

            string sql = " SELECT [RoleID] ,[MenuID],[CreateUserID] ,[CreateDate] ,[LastUpdateUserID] ,[LastUpdateDate]  FROM [dbo].[RoleMenu] where [RoleID]=@RoleID";

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@RoleID", SqlDbType.Int);
            paras[0].Value = roleID;

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, paras))
            {
                while (sdr.Read())
                {
                    RoleMenu roleMenu = new RoleMenu();
                    roleMenu.RoleID = sdr.GetInt32(0);
                    roleMenu.MenuID = sdr.GetInt32(1);
                    roleMenu.CreateUserID = sdr.IsDBNull(2) ? null : sdr.GetString(2);
                    roleMenu.CreateDate = sdr.IsDBNull(3) ? DateTime.MinValue : sdr.GetDateTime(3);
                    roleMenu.LastUpdateUserID = sdr.IsDBNull(4) ? null : sdr.GetString(4);
                    roleMenu.LastUpdateDate = sdr.IsDBNull(5) ? DateTime.MinValue : sdr.GetDateTime(5);

                    list.Add(roleMenu);
                }
            }
            return list;
        }

    }
}
